﻿set ansi_padding, ansi_warnings, concat_null_yields_null, arithabort, quoted_identifier, ansi_nulls on
go

if exists (select * from tempdb..sysobjects where id=object_id('tempdb..#tmpProperties')) drop table #tmpProperties
go

create table #tmpProperties ( name nvarchar(255), value nvarchar(255) )
go

insert into #tmpProperties values('script', 'update.0003.sql')
insert into #tmpProperties values('error', '0')

set xact_abort on
go

set transaction isolation level serializable
go

begin transaction
go

set ansi_nulls on
set quoted_identifier on
go
/* BEGIN UPDATE SCRIPT */




if object_id('[dbo].[GetTopicNamesByCallId]') is not null drop function [dbo].[GetTopicNamesByCallId]
go
create function [dbo].[GetTopicNamesByCallId] (@callId uniqueidentifier, @separator nvarchar(50), @default nvarchar(255))
	returns nvarchar(max)
as
	begin
	declare @result nvarchar(max)
	set @result = ''
	select @result = case when @result = '' then name else @result + @separator + name end from dbo.GetTopicsByCallIdAsTable(@callId) order by name
	if len(@result) > 0
		set @result = substring(@result, len(@separator), len(@result))
	else
		set @result = @default
	return @result
	end
go

if object_id('[dbo].[GetTopicsByCallIdAsTable]') is not null drop function [dbo].[GetTopicsByCallIdAsTable]
go
create function [dbo].[GetTopicsByCallIdAsTable] (@callId uniqueidentifier)
	returns @result table (
		id uniqueidentifier,
		name nvarchar(255),
		parent_id uniqueidentifier,
		description nvarchar(max),
		sort_order int
	)
as
	begin
	insert into @result
		select	id, name, parent_id, description, sort_order
		from	dbo.topics
		order by sort_order desc, name asc
	return
	end
go

if object_id('[dbo].[GetAllTopicsAsHierarchyTable]') is not null drop function [dbo].[GetAllTopicsAsHierarchyTable]
go
create function [dbo].[GetAllTopicsAsHierarchyTable] (@maxLevel int, @parentId uniqueidentifier)
	returns @result table (
		id uniqueidentifier,
		name nvarchar(255),
		parent_id uniqueidentifier,
		description nvarchar(max),
		sort_order int,
		level int
	)
as
	begin
	with allTopics(id, name, parent_id, description, sort_order, level) as (
	select	id, name, parent_id, description, sort_order, level = 1
	from	dbo.topics
	where	parent_id is null

	union all

	select 	t.id, t.name, t.parent_id, t.description, t.sort_order, level = a.level + 1
	from	dbo.topics t
			join allTopics a on t.parent_id = a.id
	)
	insert into @result select * from allTopics
	return
	end
go


if object_id('[dbo].[GetCallTopicsAsTable]') is not null drop proc [dbo].[GetCallTopicsAsTable]
go
create proc [dbo].[GetCallTopicsAsTable] @callId uniqueidentifier
as
	select * from dbo.GetTopicsByCallIdAsTable(@callId)
go


if object_id('[dbo].[GetCallHistoryForInboundCall]') is not null drop proc [dbo].[GetCallHistoryForInboundCall]
go
create proc [dbo].[GetCallHistoryForInboundCall] @customerId uniqueidentifier, @callId uniqueidentifier
as
	select	c.*,
			dbo.GetTopicNamesByCallId(c.id, ', ', '') as TOPICS
	from	dbo.vwCalls c
	where	c.id <> @callId
			and c.parent_Id = @customerId
	order by c.date_end desc
go



/* END UPDATE SCRIPT */
go
if @@error<>0 and @@trancount>0 rollback transaction
go

if @@trancount=0 begin
	update #tmpProperties set value = 1 where name = 'error'
	begin transaction
end
go

if (select top 1 value from #tmpProperties where name = 'error') <> '0' rollback transaction
go

if @@trancount > 0 begin
	declare @@scriptName nvarchar(255)
	select top 1 @@scriptName = value from #tmpProperties where name = 'script'

	if dbo.scriptExisted(@@scriptName) = 1 begin
		rollback transaction
		print @@scriptName + ' already executes'
	end
	else begin
		exec dbo.updateScript @@scriptName
		commit transaction
		print 'The database update succeeded'
	end
END
else begin
	print 'The database update failed'
end
go

drop table #tmpProperties
go
